package com.zhang.admin.service;

import com.zhang.admin.bean.Role;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @title: 角色服务
 * @author: zhangyf
 * @date: 2023/3/15 15:59
 **/
public class RoleService {

    /**
     * 获取所有角色信息
     * @return
     */
    public static List<Role> getAllRole(){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            var selectStatement = connection.prepareStatement(
                    "select id,role_name from role"
            );
            var resultSet = selectStatement.executeQuery();
            var roles = new ArrayList<Role>();
            while (resultSet.next()){
                var role = new Role();
                role.setId(resultSet.getLong("id"));
                role.setRoleName(resultSet.getString("role_name"));
                roles.add(role);
            }
            selectStatement.close();
            connection.close();
            return roles;
        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 根据用户ID获取对应的角色名称
     * @param userId
     * @return
     */
    public static String getUserRoleName(Long userId){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            var selectStatement = connection.prepareStatement(
                    "select role.role_name as roleName from user_role" +
                            " inner join role on user_role.role_id = role.id" +
                            " where user_role.user_id = ?"
            );
            selectStatement.setLong(1,userId);
            var resultSet = selectStatement.executeQuery();
            var roleName = "";
            if (resultSet.next()){
                roleName = resultSet.getString("roleName");
            }
            selectStatement.close();
            connection.close();
            return roleName;
        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 添加新角色
     * @param roleName
     */
    public static void addRole(String roleName){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            var insertStatement = connection.prepareStatement(
                    "insert into role (role_name) values (?)"
            );
            insertStatement.setString(1,roleName);

            insertStatement.close();
            connection.close();
        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    /**
     * 根据角色id删除一个角色，同时删除 角色-权限表对应的数据
     *  TODO 事务处理
     * @param roleId
     */
    public static void deleteRole(Long roleId){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            // 在角色表删除数据
            var deleteRoleStatement = connection.prepareStatement(
                    "delete from role where id = ?"
            );
            deleteRoleStatement.setLong(1,roleId);
            deleteRoleStatement.execute();

            // 在角色-权限表中删除数据
            var deleteRolePermissionStatement = connection.prepareStatement(
                    "delete from role_permission where role_id = ?"
            );
            deleteRolePermissionStatement.setLong(1,roleId);
            deleteRolePermissionStatement.execute();

            // 在用户-角色表中删除数据
            var deleteUserRoleStatement = connection.prepareStatement(
                    "delete from user_role where role_id = ?"
            );
            deleteUserRoleStatement.setLong(1,roleId);
            deleteUserRoleStatement.execute();

            // 关闭连接
            deleteRoleStatement.close();
            deleteRolePermissionStatement.close();
            deleteUserRoleStatement.close();
            connection.close();
        }catch (SQLException | ClassNotFoundException e){

            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        System.out.println(RoleService.getUserRoleName(1L));
        System.out.println(RoleService.getAllRole());
    }
}
